package cz.acies.organizer.data.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

import cz.acies.data.DBConnector;
import cz.acies.data.dao.AbstractDAO;
import cz.acies.data.persist.Country;
import cz.acies.organizer.data.persist.ContactRecord;
import cz.acies.organizer.data.persist.PostalAddress;
import cz.acies.uniql.sql.SelectQuery;
import cz.acies.uniql.sql.element.UniQLCondition;
import cz.acies.utils.UIDGenerator;

public class ContactRecordDAO extends AbstractDAO
{

	//public static void main(String[] args) {}

	public ContactRecordDAO() {}

	public List<ContactRecord> getRecordsAll() {
		List<ContactRecord> records = new ArrayList<ContactRecord>();
		Connection conn = null; 
		try {
			/*
			String sql = "SELECT * FROM contact_record LIMIT 3";
			QueryRunner qr = new QueryRunner(DBConnector.instance().getDataSource());
			ResultSetHandler<List<ContactRecord>> handler = new BeanListHandler<ContactRecord>(ContactRecord.class);
			records = qr.query(sql, handler);
			*/
			conn = DBConnector.instance().conn();
			String sql = "SELECT * FROM contact_record"; 
			QueryRunner qr = new QueryRunner();
			List<Map<String, Object>> list = (List<Map<String, Object>>) qr.query(conn,	sql, new MapListHandler());
			for (Map<String, Object> row : list) {
				String id = (String) row.get("UID");
				ContactRecord record = new ContactRecord(id, row);
				record.mapToData(row);
				records.add(record);
			}
			System.out.println("ContactRecordDAO.getRecordsAll():\n"+records);
		} catch (Exception exc) {
			exc.printStackTrace();
		} finally {
			try { if (conn != null) conn.close(); } catch (Exception exc) {}
		}
		return records;
	}
	
	public List<ContactRecord> getRecordsBy(Map<String, String> where) {
		List<ContactRecord> records = new ArrayList<ContactRecord>();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			conn = DBConnector.instance().conn();
			stmt = conn.createStatement();

			String sql = "SELECT * FROM contact_record ";
			if (where != null && !where.isEmpty()) {
				sql += "WHERE ";
				//for (String key : where.keySet()) {
				for (Iterator<String> iterator = where.keySet().iterator(); iterator.hasNext();) {
					String key = (String) iterator.next();
					String value = (String) where.get(key);
					sql += key + " = '" + value + "' ";
					if (iterator.hasNext()) sql += "AND "; 
				}
			}
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				String id = rs.getString("UID");
				ContactRecord record = new ContactRecord(id);

				record.setFirstName(rs.getString("FIRST_NAME"));
//$				rs.getString("MIDDLE_NAME");
				record.setLastName(rs.getString("LAST_NAME"));
				
				record.setTitle(rs.getString("TITLE"));
				record.setSufix(rs.getString("SUFIX"));
				record.setCompany(rs.getString("COMPANY"));
				record.setJobTitle(rs.getString("JOB_TITLE"));
				record.setNickName(rs.getString("NICK_NAME"));
//$				record.setHomeAddress(rs.getString("HOME_ADDRESS"));
//$				record.setHomePhone(rs.getString("HOME_PHONE"));
//$				record.setWorkAddress(rs.getString("WORK_ADDRESS"));
//$				record.setWorkPhone(rs.getString("WORK_PHONE"));
//$				record.setMobile(rs.getString("MOBILE"));
//$				record.setEmail(rs.getString("EMAIL"));
				record.setWebPage(rs.getString("WEB_PAGE"));
				record.setNote(rs.getString("NOTE"));

//$				record.setGoogleSyncId(rs.getString("G_SYNC_ID"));
				records.add(record);
			}
		} catch (Exception exc) {
			exc.printStackTrace();
		} finally {
			try { if (rs != null) rs.close(); } catch (Exception exc) {}
			try { if (stmt != null) stmt.close(); } catch (Exception exc) {}
			try { if (conn != null) conn.close(); } catch (Exception exc) {}
		}
		return records;
	}
	
	public ContactRecord getRecordBy(Map<String, String> where) {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			conn = DBConnector.instance().conn();
			stmt = conn.createStatement();
			
			SelectQuery sql = new SelectQuery(dbmd()).addFrom("contact_record");
			
			
			String query = "SELECT * FROM contact_record ";
			if (where != null && !where.isEmpty()) {
				query += "WHERE ";
				//for (String key : where.keySet()) {
				for (Iterator<String> iterator = where.keySet().iterator(); iterator.hasNext();) {
					String key = (String) iterator.next();
					String value = (String) where.get(key);
					query += key + " = '" + value + "' ";
					sql.add(new UniQLCondition("key", "=", "'"+value+"'"), "AND");
					if (iterator.hasNext()) { query += "AND "; }
				}
			}
			//System.out.println(query);
			rs = stmt.executeQuery(sql.toSQLString(true));
			if (rs.first()) {
				String id = rs.getString("UID");
				ContactRecord record = new ContactRecord(id);

				record.setFirstName(rs.getString("FIRST_NAME"));
//$				rs.getString("MIDDLE_NAME");
				record.setLastName(rs.getString("LAST_NAME"));
				
				record.setTitle(rs.getString("TITLE"));
				record.setSufix(rs.getString("SUFIX"));
				record.setCompany(rs.getString("COMPANY"));
				record.setJobTitle(rs.getString("JOB_TITLE"));
				record.setNickName(rs.getString("NICK_NAME"));
//$				record.setHomeAddress(rs.getString("HOME_ADDRESS"));
//$				record.setHomePhone(rs.getString("HOME_PHONE"));
//$				record.setWorkAddress(rs.getString("WORK_ADDRESS"));
//$				record.setWorkPhone(rs.getString("WORK_PHONE"));
//$				record.setMobile(rs.getString("MOBILE"));
//$				record.setEmail(rs.getString("EMAIL"));
				record.setWebPage(rs.getString("WEB_PAGE"));
				record.setNote(rs.getString("NOTE"));

//$				record.setGoogleSyncId(rs.getString("G_SYNC_ID"));
				
				return record;
			}
		} catch (Exception exc) {
			exc.printStackTrace();
		} finally {
			try { if (rs != null) rs.close(); } catch (Exception exc) {}
			try { if (stmt != null) stmt.close(); } catch (Exception exc) {}
			try { if (conn != null) conn.close(); } catch (Exception exc) {}
		}
		return null;
	}
	
	public void doAppend(ContactRecord record) {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			String uid = UIDGenerator.createUID("CT-R-");
//$			String folderId = "CT-F-01010000";
						
			String query = "";
			
			String columns = 
				"UID, " +
				//"FOLDER_ID, " +
				"FIRST_NAME, " +
				"LAST_NAME, " +
				"TITLE, " +
				"SUFIX, " +
				"COMPANY, " +
				"JOB_TITLE, " +
				"HOME_ADDRESS, " +
				"HOME_PHONE, " +
				"MOBILE, " +
				"EMAIL, " +
				"NOTE, " +

				"WORK_ADDRESS" +
				"WORK_PHONE" +

				"FNBL_SYNC_STATUS, " +
				
				"G_SYNC_STATUS, " +
				"G_SYNC_ID, " +
				
				"WRITER_ID, " +
				"WRITE_DATE, " +
				"EDITOR_ID, " +
				"EDIT_DATE";
			String values = 
				"'"+uid+"', " +
				//"'"+record.getfolderId+"', " +
				"'"+record.getFirstName()+"', " +
				"'"+record.getLastName()+"', " +
				"'"+record.getTitle()+"', " +
				"'"+record.getSufix()+"', " +
				"'"+record.getCompany()+"', " +
				"'"+record.getJobTitle()+"', " +

				"'"+record.getHomeAddress()+"', " +
//$				"'"+record.getHomePhone()+"', " +
				"'"+record.getMobile()+"', " +
				"'"+record.getEmail()+"', " +
				"'"+record.getNote()+"', " +

				"'"+record.getWorkAddress()+"', " +
//$				"'"+record.getWorkPhone()+"', " +

				"'N', " +
				"'U', " +
//$				"'"+record.getGoogleSyncId()+"', " +
				
				"'W_ADMIN', " +
				"NOW(), " +
				"'W_ADMIN', " +
				"NOW()";
			
			query = "INSERT INTO contact_record (" + columns + ") VALUES (" + values + ")";
				
			System.out.println("CalendarEventDialog.okPressed().insert:\n" + query);
			
			conn = DBConnector.instance().conn();
			stmt = conn.createStatement();
			stmt.executeUpdate(query);
		} catch (Exception exc) {
			exc.printStackTrace();
		} finally {
			try { if (rs != null) rs.close(); } catch (Exception e) {}
			try { if (stmt != null) stmt.close(); } catch (Exception e) {}
			try { if (conn != null) conn.close(); } catch (Exception e) {}
		}
	}
	
	public void doUpdate(ContactRecord record) {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		try {
			String query =  
			"UPDATE contact_record SET " +
				//"`FOLDER_ID` = '"+getFolderId()+"', " +
				"`FIRST_NAME` = '"+record.getFirstName()+"', " +
				"`LAST_NAME` = '"+record.getLastName()+"', " +
				"`TITLE` = '"+record.getTitle()+"', " +
				"`SUFIX` = '"+record.getSufix()+"', " +
				"`COMPANY` = '"+record.getCompany()+"', " +
				"`JOB_TITLE` = '"+record.getJobTitle()+"', " +

				"`HOME_ADDRESS` = '"+record.getHomeAddress()+"', " +
//$				"`HOME_PHONE` = '"+record.getHomePhone()+"', " +
				"`MOBILE` = '"+record.getMobile()+"', " +
				"`EMAIL` = '"+record.getEmail()+"', " +
				"`NOTE` = '"+record.getNote()+"', " +

				"`WORK_ADDRESS` = '"+record.getWorkAddress()+"', " +
//$				"`WORK_PHONE` = '"+record.getWorkPhone()+"', " +

				"`FNBL_SYNC_STATUS` = 'U', " +
				
				"`G_SYNC_STATUS` = 'U', " +
//$				"`G_SYNC_ID` = '"+record.getGoogleSyncId()+"', " +
				
				"`EDITOR_ID` = 'W_ADMIN', " +
				"`EDIT_DATE` = NOW() " +
			"WHERE contact_record.UID = '"+record.getId()+"'";
				
			System.out.println("CalendarEventDialog.okPressed().update:\n" + query);
			
			conn = DBConnector.instance().conn();
			stmt = conn.createStatement();
			stmt.executeUpdate(query);
		} catch (Exception exc) {
			exc.printStackTrace();
		} finally {
			try { if (rs != null) rs.close(); } catch (Exception e) {}
			try { if (stmt != null) stmt.close(); } catch (Exception e) {}
			try { if (conn != null) conn.close(); } catch (Exception e) {}
		}
				
	}
	
	public void doDelete(ContactRecord record) {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			String query = "DELETE FROM calendar_record WHERE calendar_record.UID = '" + record.getId() + "'";
			conn = DBConnector.instance().conn();
			stmt = conn.createStatement();
			System.out.println("ContactRecord.doDelete().sql:\n" + query);
			//stmt.executeUpdate(query);
		} catch (Exception exc) {
			exc.printStackTrace();
		} finally {
			try { if (rs != null) rs.close(); } catch (Exception e) {}
			try { if (stmt != null) stmt.close(); } catch (Exception e) {}
			try { if (conn != null) conn.close(); } catch (Exception e) {}
		}		
	}

}
